"""
表格数据专家代理 - 负责审查表格数据的准确性和完整性
"""

from typing import Dict, List, Any, Optional
import time
import json
import pandas as pd
from pathlib import Path
from loguru import logger

from .base_agent import BaseAgent, AgentResult, ReviewItem
from ..utils.data_loader import ProcessingElement


class TableDataAgent(BaseAgent):
    """表格数据专家代理"""
    
    def __init__(self, api_client, config: Dict[str, Any]):
        super().__init__(
            name="表格数据专家",
            agent_type="table_data",
            api_client=api_client,
            config=config
        )
    
    def get_system_prompt(self) -> str:
        """获取系统提示词"""
        return """你是表格数据审查专家，负责审查城市更新方案中的表格数据准确性和完整性。

审查重点和评分标准：
- 数据准确性（25分）：数值计算、统计汇总是否正确，单位换算是否准确
- 数据完整性（25分）：必要数据是否齐全，关键字段是否有缺失
- 数据一致性（20分）：同类数据在不同表格中是否一致，前后数据是否矛盾
- 逻辑合理性（15分）：数据之间的逻辑关系是否合理，是否符合常识
- 格式规范性（15分）：表格格式、小数位数、对齐方式是否规范

评分依据：
- 每个检查项目根据问题严重程度扣分：critical(-5分)，major(-3分)，minor(-1分)
- confidence值基于检查的全面性和问题识别的准确性计算
- 总分100分，各项得分累加后计算最终评分

输出JSON格式：
{
    "findings": [
        {
            "item_id": "table_001",
            "category": "表格数据",
            "severity": "critical/major/minor/info",
            "description": "具体问题描述",
            "suggestion": "改进建议",
            "table_name": "表格名称",
            "location": "问题位置（行列）",
            "data_type": "数据类型",
            "confidence": 0.9,
            "score_impact": "对评分的影响（扣分值）",
            "check_item": "检查项目（准确性/完整性/一致性/逻辑性/规范性）"
        }
    ],
    "summary": "表格数据整体评估摘要",
    "data_analysis": {
        "total_tables": "表格总数",
        "table_types": ["识别的表格类型"],
        "data_quality": "数据质量评估",
        "completeness": "完整性评估"
    },
    "scoring_details": {
        "accuracy_score": "数据准确性得分（0-25）",
        "completeness_score": "数据完整性得分（0-25）", 
        "consistency_score": "数据一致性得分（0-20）",
        "logic_score": "逻辑合理性得分（0-15）",
        "format_score": "格式规范性得分（0-15）",
        "total_score": "总分（0-100）",
        "score_breakdown": "详细扣分说明"
    }
}"""
    
    def process(self, data: Any, context: Dict[str, Any] = None) -> AgentResult:
        """处理数据并返回表格数据审查结果"""
        start_time = time.time()
        
        if not self.validate_input(data):
            return self.handle_api_error(
                Exception("输入数据无效"), 
                "表格数据审查"
            )
        
        try:
            # 提取表格数据
            table_data = self._extract_table_data(data)
            
            if not table_data:
                return self.create_result(
                    success=True,
                    findings=[],
                    summary="无表格数据需要审查",
                    confidence=1.0,
                    processing_time=time.time() - start_time
                )
            
            self.log_processing_start(f"表格数据审查，表格数量: {len(table_data)}")
            
            # 处理所有表格
            all_review_items = []
            data_analysis = {
                "total_tables": len(table_data),
                "table_types": [],
                "data_quality": "",
                "completeness": ""
            }
            
            for i, table_info in enumerate(table_data):
                try:
                    # 审查单个表格
                    review_items = self._review_single_table(table_info, i, context)
                    all_review_items.extend(review_items)
                    
                    # 分析表格类型
                    table_type = self._identify_table_type(table_info)
                    if table_type and table_type not in data_analysis["table_types"]:
                        data_analysis["table_types"].append(table_type)
                        
                except Exception as e:
                    logger.error(f"审查表格 {i} 失败: {e}")
                    all_review_items.append(self.create_review_item(
                        item_id=f"table_error_{i}",
                        category="处理错误",
                        severity="major",
                        description=f"表格 {i} 处理失败: {str(e)}",
                        suggestion="请检查表格格式和数据"
                    ))
            
            # 评估整体数据质量和完整性
            data_analysis["data_quality"] = self._assess_data_quality(all_review_items)
            data_analysis["completeness"] = self._assess_completeness(data_analysis["table_types"])
            
            # 生成结果
            processing_time = time.time() - start_time
            findings = self.format_findings(all_review_items)
            summary = self.generate_summary(all_review_items)
            confidence = self.calculate_confidence(all_review_items)
            
            self.log_processing_end(True, processing_time, len(findings))
            
            return self.create_result(
                success=True,
                findings=findings,
                summary=summary,
                confidence=confidence,
                processing_time=processing_time,
                metadata={
                    "total_tables": len(table_data),
                    "data_analysis": data_analysis,
                    "agent_type": self.agent_type
                }
            )
            
        except Exception as e:
            return self.handle_api_error(e, "表格数据审查")
    
    def _extract_table_data(self, data: Any) -> List[Dict[str, Any]]:
        """提取表格数据"""
        table_data = []
        
        if isinstance(data, list):
            for item in data:
                if isinstance(item, ProcessingElement):
                    # 检查是否为表格类型 - 修复：检查subcategory和metadata字段
                    if (hasattr(item, 'subcategory') and item.subcategory == 'general') or \
                       (hasattr(item, 'category') and item.category == 'table') or \
                       (hasattr(item, 'source_type') and item.source_type == 'table') or \
                       (hasattr(item, 'metadata') and item.metadata and 
                        item.metadata.get('element_type') == 'Table'):
                        table_data.append({
                            'content': item.content,
                            'element_id': item.element_id,
                            'metadata': item.metadata
                        })
                elif isinstance(item, dict):
                    # 检查字典格式的数据 - 修复：检查subcategory和metadata字段
                    if (item.get('subcategory') == 'general') or \
                       (item.get('category') == 'table') or \
                       (item.get('source_type') == 'table') or \
                       (item.get('metadata', {}).get('element_type') == 'Table') or \
                       'table' in str(item.get('category', '')).lower():
                        table_data.append(item)
        elif isinstance(data, ProcessingElement):
            # 单个ProcessingElement对象
            if (hasattr(data, 'subcategory') and data.subcategory == 'general') or \
               (hasattr(data, 'category') and data.category == 'table') or \
               (hasattr(data, 'source_type') and data.source_type == 'table') or \
               (hasattr(data, 'metadata') and data.metadata and 
                data.metadata.get('element_type') == 'Table'):
                table_data.append({
                    'content': data.content,
                    'element_id': data.element_id,
                    'metadata': data.metadata
                })
        elif isinstance(data, dict):
            # 单个字典对象
            if (data.get('subcategory') == 'general') or \
               (data.get('category') == 'table') or \
               (data.get('source_type') == 'table') or \
               (data.get('metadata', {}).get('element_type') == 'Table') or \
               'table' in str(data.get('category', '')).lower():
                table_data.append(data)
        
        return table_data
    
    def _review_single_table(self, table_info: Dict[str, Any], index: int, context: Dict[str, Any] = None) -> List[ReviewItem]:
        """审查单个表格"""
        content = table_info.get('content', '')
        
        if not content:
            return [self.create_review_item(
                item_id=f"table_empty_{index}",
                category="数据问题",
                severity="major",
                description=f"表格 {index} 内容为空",
                suggestion="请检查表格数据提取"
            )]
        
        try:
            # 解析表格数据
            parsed_data = self._parse_table_content(content)
            
            # 进行数据验证
            validation_items = self._validate_table_data(parsed_data, index)
            
            # 构建表格审查提示
            prompt = self._build_table_review_prompt(table_info, parsed_data, context)
            
            # 调用LLM进行表格分析，添加错误处理和重试
            max_retries = 3
            for attempt in range(max_retries):
                try:
                    response = self.api_client.chat_completion(
                        messages=[
                            {"role": "system", "content": self.get_system_prompt()},
                            {"role": "user", "content": prompt}
                        ],
                        model=self.config.get('text_model', 'Qwen/Qwen2.5-7B-Instruct'),
                        max_tokens=2000,  # 限制输出长度
                        temperature=0.3   # 降低随机性
                    )
                    break  # 成功则跳出重试循环
                except Exception as api_error:
                    logger.warning(f"表格 {index} API调用失败 (尝试 {attempt + 1}/{max_retries}): {api_error}")
                    if attempt == max_retries - 1:
                        # 最后一次尝试失败，返回基础验证结果
                        logger.error(f"表格 {index} API调用最终失败，返回基础验证结果")
                        return validation_items + [self.create_review_item(
                            item_id=f"table_api_error_{index}",
                            category="API错误",
                            severity="major",
                            description=f"表格分析API调用失败: {str(api_error)}",
                            suggestion="请检查网络连接或稍后重试"
                        )]
                    time.sleep(1)  # 重试前等待1秒
            
            # 解析响应
            llm_review_items = self.parse_llm_response(response)
            
            # 合并验证结果和LLM分析结果
            all_items = validation_items + llm_review_items
            
            # 为每个审查项添加表格信息
            for item in all_items:
                if hasattr(item, 'metadata'):
                    item.metadata = item.metadata or {}
                    item.metadata.update({
                        'table_index': index,
                        'element_id': table_info.get('element_id', ''),
                        'table_type': self._identify_table_type(table_info)
                    })
            
            return all_items
            
        except Exception as e:
            logger.error(f"审查表格 {index} 时发生错误: {e}")
            return [self.create_review_item(
                item_id=f"table_analysis_error_{index}",
                category="分析错误",
                severity="major",
                description=f"表格分析失败: {str(e)}",
                suggestion="请检查表格格式或重新提交"
            )]
    
    def _parse_table_content(self, content: str) -> Dict[str, Any]:
        """解析表格内容"""
        parsed_data = {
            "raw_content": content,
            "rows": [],
            "columns": [],
            "numeric_data": [],
            "has_headers": False,
            "data_types": {}
        }
        
        try:
            # 尝试解析JSON格式的表格数据
            if content.strip().startswith('{') or content.strip().startswith('['):
                json_data = json.loads(content)
                if isinstance(json_data, dict):
                    parsed_data.update(json_data)
                elif isinstance(json_data, list):
                    parsed_data["rows"] = json_data
            else:
                # 解析文本格式的表格
                lines = content.strip().split('\n')
                for line in lines:
                    if line.strip():
                        # 简单的分隔符检测
                        if '\t' in line:
                            row = line.split('\t')
                        elif ',' in line:
                            row = line.split(',')
                        elif '|' in line:
                            row = [cell.strip() for cell in line.split('|') if cell.strip()]
                        else:
                            row = [line.strip()]
                        
                        parsed_data["rows"].append(row)
                
                # 检测表头
                if parsed_data["rows"]:
                    first_row = parsed_data["rows"][0]
                    parsed_data["has_headers"] = any(
                        not self._is_numeric(cell) for cell in first_row
                    )
                    
                    if parsed_data["has_headers"]:
                        parsed_data["columns"] = first_row
                
                # 提取数值数据
                start_row = 1 if parsed_data["has_headers"] else 0
                for row in parsed_data["rows"][start_row:]:
                    numeric_row = []
                    for cell in row:
                        if self._is_numeric(cell):
                            numeric_row.append(float(cell.replace(',', '')))
                        else:
                            numeric_row.append(cell)
                    parsed_data["numeric_data"].append(numeric_row)
        
        except Exception as e:
            logger.warning(f"解析表格内容失败: {e}")
            parsed_data["parse_error"] = str(e)
        
        return parsed_data
    
    def _is_numeric(self, value: str) -> bool:
        """检查字符串是否为数值"""
        try:
            # 移除常见的数字格式字符
            clean_value = str(value).replace(',', '').replace('%', '').replace('￥', '').replace('$', '').strip()
            float(clean_value)
            return True
        except (ValueError, TypeError):
            return False
    
    def _validate_table_data(self, parsed_data: Dict[str, Any], index: int) -> List[ReviewItem]:
        """验证表格数据"""
        validation_items = []
        
        # 检查表格是否为空
        if not parsed_data.get("rows"):
            validation_items.append(self.create_review_item(
                item_id=f"table_no_data_{index}",
                category="数据完整性",
                severity="critical",
                description="表格无数据行",
                suggestion="请确保表格包含有效数据"
            ))
            return validation_items
        
        # 检查数据一致性
        rows = parsed_data.get("rows", [])
        if len(rows) > 1:
            row_lengths = [len(row) for row in rows]
            if len(set(row_lengths)) > 1:
                validation_items.append(self.create_review_item(
                    item_id=f"table_inconsistent_columns_{index}",
                    category="数据格式",
                    severity="major",
                    description=f"表格行列数不一致: {row_lengths}",
                    suggestion="请确保所有行的列数相同"
                ))
        
        # 检查数值数据的合理性
        numeric_data = parsed_data.get("numeric_data", [])
        for row_idx, row in enumerate(numeric_data):
            for col_idx, value in enumerate(row):
                if isinstance(value, (int, float)):
                    # 检查异常值
                    if value < 0 and col_idx > 0:  # 假设第一列可能是负数（如变化量）
                        validation_items.append(self.create_review_item(
                            item_id=f"table_negative_value_{index}_{row_idx}_{col_idx}",
                            category="数据异常",
                            severity="minor",
                            description=f"发现负数值: {value} (行{row_idx+1}, 列{col_idx+1})",
                            suggestion="请确认负数值是否合理"
                        ))
                    
                    if abs(value) > 1e10:  # 检查过大的数值
                        validation_items.append(self.create_review_item(
                            item_id=f"table_large_value_{index}_{row_idx}_{col_idx}",
                            category="数据异常",
                            severity="minor",
                            description=f"发现异常大的数值: {value} (行{row_idx+1}, 列{col_idx+1})",
                            suggestion="请确认数值大小和单位是否正确"
                        ))
        
        return validation_items
    
    def _build_table_review_prompt(self, table_info: Dict[str, Any], parsed_data: Dict[str, Any], context: Dict[str, Any] = None) -> str:
        """构建表格审查提示"""
        prompt_parts = [
            "对以下表格数据进行专业审查，重点关注数据的准确性、完整性和合理性：",
            "",
            "=== 表格内容 ===",
            f"表格ID: {table_info.get('element_id', '未知')}",
            f"数据行数: {len(parsed_data.get('rows', []))}",
            f"是否有表头: {'是' if parsed_data.get('has_headers') else '否'}",
        ]
        
        # 添加表格内容摘要
        if parsed_data.get("columns"):
            columns_str = ', '.join(parsed_data['columns'][:10])  # 限制列数
            if len(parsed_data['columns']) > 10:
                columns_str += f"... (共{len(parsed_data['columns'])}列)"
            prompt_parts.extend([
                f"列标题: {columns_str}",
            ])
        
        # 添加前几行数据作为示例，并限制内容长度
        rows = parsed_data.get("rows", [])
        if rows:
            prompt_parts.extend([
                "",
                "=== 数据示例 ===",
            ])
            max_rows = min(3, len(rows))  # 最多显示3行
            for i in range(max_rows):
                row = rows[i]
                # 限制每行的内容长度
                row_content = []
                for cell in row[:8]:  # 最多显示8列
                    cell_str = str(cell)
                    if len(cell_str) > 50:  # 限制单元格内容长度
                        cell_str = cell_str[:47] + "..."
                    row_content.append(cell_str)
                
                if len(row) > 8:
                    row_content.append(f"... (共{len(row)}列)")
                
                prompt_parts.append(f"第{i+1}行: {', '.join(row_content)}")
            
            if len(rows) > max_rows:
                prompt_parts.append(f"... (共{len(rows)}行)")
        
        prompt_parts.extend([
            "",
            "=== 审查要点 ===",
            "1. 数据准确性：数值计算、统计是否正确",
            "2. 数据完整性：必要数据是否齐全",
            "3. 数据一致性：同类数据是否一致",
            "4. 逻辑合理性：数据关系是否合理",
            "5. 单位规范：数据单位是否正确统一",
            "6. 格式规范：表格格式是否规范",
            "7. 异常值检测：是否存在明显异常的数据",
        ])
        
        # 添加表格元数据信息（限制长度）
        if table_info.get('metadata'):
            metadata = table_info['metadata']
            prompt_parts.extend([
                "",
                "=== 表格信息 ===",
            ])
            
            if isinstance(metadata, dict):
                for key, value in metadata.items():
                    if key not in ['content', 'element_id']:
                        value_str = str(value)
                        if len(value_str) > 100:  # 限制元数据长度
                            value_str = value_str[:97] + "..."
                        prompt_parts.append(f"{key}: {value_str}")
        
        if context:
            prompt_parts.extend([
                "",
                "=== 项目背景 ===",
                f"项目类型: {context.get('project_type', '城市更新')}",
                f"项目阶段: {context.get('project_stage', '方案设计')}",
            ])
        
        prompt_parts.extend([
            "",
            "按照系统提示中的JSON格式输出详细的审查结果，必须包含详细的评分信息和扣分依据。"
        ])
        
        # 检查总长度，如果过长则进一步截断
        full_prompt = "\n".join(prompt_parts)
        if len(full_prompt) > 4000:  # 限制总长度
            # 进一步减少数据示例
            prompt_parts = prompt_parts[:prompt_parts.index("=== 数据示例 ===")]
            prompt_parts.extend([
                "=== 数据示例 ===",
                f"表格包含{len(rows)}行数据，由于内容较长，仅提供基本信息进行审查。",
                "",
                "=== 审查要点 ===",
                "1. 数据准确性：数值计算、统计是否正确",
                "2. 数据完整性：必要数据是否齐全",
                "3. 数据一致性：同类数据是否一致",
                "4. 逻辑合理性：数据关系是否合理",
                "5. 单位规范：数据单位是否正确统一",
                "6. 格式规范：表格格式是否规范",
                "7. 异常值检测：是否存在明显异常的数据",
                "",
                "请按照系统提示中的JSON格式输出详细的审查结果。"
            ])
        
        return "\n".join(prompt_parts)
    
    def _identify_table_type(self, table_info: Dict[str, Any]) -> str:
        """识别表格类型"""
        element_id = table_info.get('element_id', '')
        metadata = table_info.get('metadata', {})
        content = table_info.get('content', '')
        
        # 基于内容和元数据识别表格类型
        text_to_analyze = f"{element_id} {str(metadata)} {content}".lower()
        
        table_types = {
            '建设指标表': ['建设指标', '容积率', '绿化率', '建筑面积', '用地面积'],
            '投资估算表': ['投资', '估算', '费用', '成本', '预算', '金额'],
            '进度计划表': ['进度', '计划', '时间', '工期', '里程碑', '阶段'],
            '技术参数表': ['技术参数', '设计参数', '规格', '标准', '指标'],
            '对比分析表': ['对比', '比较', '分析', '方案', '评估'],
            '统计汇总表': ['统计', '汇总', '总计', '合计', '小计']
        }
        
        for table_type, keywords in table_types.items():
            if any(keyword in text_to_analyze for keyword in keywords):
                return table_type
        
        return '未识别类型'
    
    def _assess_data_quality(self, review_items: List[ReviewItem]) -> str:
        """评估数据质量"""
        if not review_items:
            return "数据质量良好"
        
        critical_count = sum(1 for item in review_items if item.severity == 'critical')
        major_count = sum(1 for item in review_items if item.severity == 'major')
        
        if critical_count > 0:
            return f"存在{critical_count}个严重数据问题"
        elif major_count > 3:
            return f"存在{major_count}个重要问题"
        elif major_count > 0:
            return f"存在{major_count}个问题，整体可接受"
        else:
            return "数据质量良好"
    
    def _assess_completeness(self, table_types: List[str]) -> str:
        """评估表格完整性"""
        required_types = ['建设指标表', '投资估算表']
        missing_types = [t for t in required_types if t not in table_types]
        
        if not missing_types:
            return "表格类型齐全"
        elif len(missing_types) == 1:
            return f"缺少{missing_types[0]}"
        else:
            return f"缺少{len(missing_types)}种重要表格类型"
    
    def calculate_detailed_score(self, review_items: List[ReviewItem]) -> dict:
        """计算详细评分"""
        # 初始分数
        scores = {
            "accuracy_score": 25,      # 数据准确性
            "completeness_score": 25,  # 数据完整性
            "consistency_score": 20,   # 数据一致性
            "logic_score": 15,         # 逻辑合理性
            "format_score": 15         # 格式规范性
        }
        
        score_breakdown = []
        
        # 根据问题严重程度扣分
        for item in review_items:
            deduction = 0
            if item.severity == "critical":
                deduction = 5
            elif item.severity == "major":
                deduction = 3
            elif item.severity == "minor":
                deduction = 1
            
            # 根据检查项目类型扣分
            check_item = getattr(item, 'check_item', '准确性')
            if '准确性' in check_item:
                scores["accuracy_score"] = max(0, scores["accuracy_score"] - deduction)
            elif '完整性' in check_item:
                scores["completeness_score"] = max(0, scores["completeness_score"] - deduction)
            elif '一致性' in check_item:
                scores["consistency_score"] = max(0, scores["consistency_score"] - deduction)
            elif '逻辑性' in check_item:
                scores["logic_score"] = max(0, scores["logic_score"] - deduction)
            elif '规范性' in check_item:
                scores["format_score"] = max(0, scores["format_score"] - deduction)
            else:
                # 默认扣准确性分数
                scores["accuracy_score"] = max(0, scores["accuracy_score"] - deduction)
            
            score_breakdown.append(f"{item.description}: -{deduction}分 ({item.severity})")
        
        scores["total_score"] = sum(scores.values())
        scores["score_breakdown"] = score_breakdown
        
        return scores